package market

import (
	"database/sql"
	"fmt"
	"log"
	"strings"
	"time"

	"github.com/gin-gonic/gin"
)

type MarketingCampaign struct {
	ID          int        `json:"id"`
	Name        string     `json:"name"`
	Subject     string     `json:"subject"`
	Content     string     `json:"content"`
	CreatorID   int        `json:"creator_id"`
	Status      string     `json:"status"` // draft, active, completed, cancelled
	ScheduledAt *time.Time `json:"scheduled_at"`
	SentAt      *time.Time `json:"sent_at"`
	CreatedAt   time.Time  `json:"created_at"`
	UpdatedAt   time.Time  `json:"updated_at"`
}

type MarketingTemplate struct {
	ID        int       `json:"id"`
	Name      string    `json:"name"`
	Subject   string    `json:"subject"`
	Content   string    `json:"content"`
	CreatorID int       `json:"creator_id"`
	IsActive  bool      `json:"is_active"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

func GetMarketingOptInRate(db *sql.DB) (float64, error) {
	var totalUsers, optedInUsers int

	err := db.QueryRow(`SELECT COUNT(*) FROM auth WHERE active = TRUE`).Scan(&totalUsers)
	if err != nil {
		return 0, err
	}

	if totalUsers == 0 {
		return 0, nil
	}

	err = db.QueryRow(`
		SELECT COUNT(*) FROM auth a 
		LEFT JOIN marketing_preferences mp ON a.id = mp.user_id
		WHERE a.active = TRUE AND (mp.email_marketing = TRUE OR mp.email_marketing IS NULL)
	`).Scan(&optedInUsers)
	if err != nil {
		return 0, err
	}

	return float64(optedInUsers) / float64(totalUsers) * 100, nil
}

func GetMarketingCampaigns(db *sql.DB, limit, offset int) ([]MarketingCampaign, error) {
	rows, err := db.Query(`
		SELECT id, name, subject, content, creator_id, status, scheduled_at, sent_at, created_at, updated_at 
		FROM marketing_campaigns 
		ORDER BY created_at DESC 
		LIMIT ? OFFSET ?
	`, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var campaigns []MarketingCampaign
	for rows.Next() {
		var campaign MarketingCampaign
		var scheduledAt, sentAt sql.NullTime

		err := rows.Scan(
			&campaign.ID, &campaign.Name, &campaign.Subject, &campaign.Content,
			&campaign.CreatorID, &campaign.Status, &scheduledAt, &sentAt,
			&campaign.CreatedAt, &campaign.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}

		if scheduledAt.Valid {
			campaign.ScheduledAt = &scheduledAt.Time
		}
		if sentAt.Valid {
			campaign.SentAt = &sentAt.Time
		}

		campaigns = append(campaigns, campaign)
	}

	return campaigns, nil
}

func CreateMarketingCampaign(db *sql.DB, name, subject, content string, creatorID int) (*MarketingCampaign, error) {
	result, err := db.Exec(`
		INSERT INTO marketing_campaigns (name, subject, content, creator_id, status) 
		VALUES (?, ?, ?, ?, 'draft')
	`, name, subject, content, creatorID)
	if err != nil {
		return nil, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return nil, err
	}

	campaign := &MarketingCampaign{
		ID:        int(id),
		Name:      name,
		Subject:   subject,
		Content:   content,
		CreatorID: creatorID,
		Status:    "draft",
		CreatedAt: time.Now(),
		UpdatedAt: time.Now(),
	}

	return campaign, nil
}

func UpdateMarketingCampaign(db *sql.DB, id int, name, subject, content string) error {
	_, err := db.Exec(`
		UPDATE marketing_campaigns 
		SET name = ?, subject = ?, content = ?, updated_at = CURRENT_TIMESTAMP 
		WHERE id = ?
	`, name, subject, content, id)

	return err
}

func ScheduleMarketingCampaign(db *sql.DB, id int, scheduledAt time.Time) error {
	_, err := db.Exec(`
		UPDATE marketing_campaigns 
		SET scheduled_at = ?, status = 'scheduled', updated_at = CURRENT_TIMESTAMP 
		WHERE id = ?
	`, scheduledAt, id)

	return err
}

func ExecuteMarketingCampaign(db *sql.DB, campaignID int) error {
	var campaign MarketingCampaign
	err := db.QueryRow(`
		SELECT id, name, subject, content, creator_id, status 
		FROM marketing_campaigns 
		WHERE id = ?
	`, campaignID).Scan(&campaign.ID, &campaign.Name, &campaign.Subject, &campaign.Content, &campaign.CreatorID, &campaign.Status)
	if err != nil {
		return err
	}

	if campaign.Status != "draft" && campaign.Status != "scheduled" {
		return nil
	}

	_, err = db.Exec(`
		UPDATE marketing_campaigns 
		SET status = 'active', updated_at = CURRENT_TIMESTAMP 
		WHERE id = ?
	`, campaignID)
	if err != nil {
		return err
	}

	users, err := GetUsersForMarketing(db)
	if err != nil {
		return err
	}

	emailID, err := LogMarketingEmail(db, campaign.Subject, campaign.Content, campaign.CreatorID)
	if err != nil {
		return err
	}

	var totalSent, successfulSent, failedSent int
	for _, user := range users {
		totalSent++
		err := SendMarketingMailWithName(user.Email, campaign.Subject, campaign.Content, user.DisplayName)
		if err != nil {
			failedSent++
			log.Printf("Failed to send marketing email to %s: %v", user.Email, err)
			LogMarketingEmailRecipient(db, emailID, user.ID, user.Email, "failed", err.Error())
		} else {
			successfulSent++
			LogMarketingEmailRecipient(db, emailID, user.ID, user.Email, "sent", "")
		}
	}

	err = UpdateMarketingEmailStats(db, emailID, totalSent, successfulSent, failedSent)
	if err != nil {
		log.Printf("Failed to update marketing email stats: %v", err)
	}

	_, err = db.Exec(`
		UPDATE marketing_campaigns 
		SET status = 'completed', sent_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP 
		WHERE id = ?
	`, campaignID)
	if err != nil {
		return err
	}

	return nil
}

func GetMarketingTemplates(db *sql.DB) ([]MarketingTemplate, error) {
	rows, err := db.Query(`
		SELECT id, name, subject, content, creator_id, is_active, created_at, updated_at 
		FROM marketing_templates 
		WHERE is_active = TRUE
		ORDER BY created_at DESC
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var templates []MarketingTemplate
	for rows.Next() {
		var template MarketingTemplate
		err := rows.Scan(
			&template.ID, &template.Name, &template.Subject, &template.Content,
			&template.CreatorID, &template.IsActive, &template.CreatedAt, &template.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		templates = append(templates, template)
	}

	return templates, nil
}

func CreateMarketingTemplate(db *sql.DB, name, subject, content string, creatorID int) (*MarketingTemplate, error) {
	result, err := db.Exec(`
		INSERT INTO marketing_templates (name, subject, content, creator_id, is_active) 
		VALUES (?, ?, ?, ?, TRUE)
	`, name, subject, content, creatorID)
	if err != nil {
		return nil, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return nil, err
	}

	template := &MarketingTemplate{
		ID:        int(id),
		Name:      name,
		Subject:   subject,
		Content:   content,
		CreatorID: creatorID,
		IsActive:  true,
		CreatedAt: time.Now(),
		UpdatedAt: time.Now(),
	}

	return template, nil
}

func GetUserMarketingHistory(db *sql.DB, userID int, limit, offset int) ([]gin.H, error) {
	rows, err := db.Query(`
		SELECT mer.marketing_email_id, mel.subject, mer.status, mer.sent_at, mer.error_message
		FROM marketing_email_recipients mer
		JOIN marketing_email_log mel ON mer.marketing_email_id = mel.id
		WHERE mer.user_id = ?
		ORDER BY mer.sent_at DESC
		LIMIT ? OFFSET ?
	`, userID, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var history []gin.H
	for rows.Next() {
		var (
			emailID      int
			subject      string
			status       string
			sentAt       sql.NullTime
			errorMessage sql.NullString
		)
		err := rows.Scan(&emailID, &subject, &status, &sentAt, &errorMessage)
		if err != nil {
			return nil, err
		}

		item := gin.H{
			"email_id": emailID,
			"subject":  subject,
			"status":   status,
		}

		if sentAt.Valid {
			item["sent_at"] = sentAt.Time.Format("2006-01-02 15:04:05")
		}

		if errorMessage.Valid {
			item["error_message"] = errorMessage.String
		}

		history = append(history, item)
	}

	return history, nil
}

func BulkUpdateMarketingPreferences(db *sql.DB, userIDs []int, emailMarketing bool) error {
	if len(userIDs) == 0 {
		return nil
	}

	placeholders := make([]string, len(userIDs))
	args := make([]interface{}, len(userIDs)+1)

	for i, id := range userIDs {
		placeholders[i] = "?"
		args[i] = id
	}
	args[len(userIDs)] = emailMarketing

	query := fmt.Sprintf(`
		INSERT INTO marketing_preferences (user_id, email_marketing) 
		VALUES %s
		ON DUPLICATE KEY UPDATE 
		email_marketing = VALUES(email_marketing), 
		updated_at = CURRENT_TIMESTAMP
	`, strings.Join(placeholders, ", "))

	_, err := db.Exec(query, args...)
	return err
}

func GetMarketingEngagementMetrics(db *sql.DB, days int) (map[string]interface{}, error) {
	metrics := make(map[string]interface{})

	var totalSent int
	err := db.QueryRow(`
		SELECT COALESCE(SUM(total_sent), 0) 
		FROM marketing_email_log 
		WHERE created_at >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL ? DAY)
	`, days).Scan(&totalSent)
	if err != nil {
		return nil, err
	}

	var delivered int
	err = db.QueryRow(`
		SELECT COALESCE(SUM(successful_sent), 0) 
		FROM marketing_email_log 
		WHERE created_at >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL ? DAY)
	`, days).Scan(&delivered)
	if err != nil {
		return nil, err
	}

	var failed int
	err = db.QueryRow(`
		SELECT COALESCE(SUM(failed_sent), 0) 
		FROM marketing_email_log 
		WHERE created_at >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL ? DAY)
	`, days).Scan(&failed)
	if err != nil {
		return nil, err
	}

	metrics["total_sent"] = totalSent
	metrics["delivered"] = delivered
	metrics["failed"] = failed

	if totalSent > 0 {
		metrics["delivery_rate"] = float64(delivered) / float64(totalSent) * 100
		metrics["failure_rate"] = float64(failed) / float64(totalSent) * 100
	} else {
		metrics["delivery_rate"] = 0.0
		metrics["failure_rate"] = 0.0
	}

	return metrics, nil
}
